package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import edu.byu.intex2.data.GUID;
import edu.byu.intex2.data.bo.TradeInLine;
import edu.byu.intex2.data.bo.UsedCar;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 * TradeInLine Data Access Object
 * @author Brady
 */
public class TradeInLineDAO {

    /////////////////////////////////////////////
    ///   Singleton code
    private static TradeInLineDAO instance = null;

    /** Creates a new instance of TradeInLineDAO */
    private TradeInLineDAO() {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized TradeInLineDAO getInstance() {
        if (instance == null) {
            instance = new TradeInLineDAO();
        }
        return instance;
    }//getInstance

    ////////////////////////////////////////////
    ///   CREATE methods
    /**
     * Creates a new tradeInLine in the database
     * @param id GUID
     * @param VIN physical_car vin of sale line
     */
    public TradeInLine create(String saleId, String vin) throws DataException {
        TradeInLine tradeInLine = new TradeInLine(GUID.generate()); // generate a guid for the tradeInLine even though we never use the guid...
        tradeInLine.setObjectAlreadyInDB(false);

        // Set Sale ID
        tradeInLine.setSaleId(saleId);

        try {
            UsedCar usedCar; // Used Car

            usedCar = UsedCarDAO.getInstance().getByVIN(vin); // might return null

            // if usedCar is null, get the UsedCar's guid by VIN
            if (usedCar == null) {
                String usedCarId = PhysicalCarDAO.getInstance().getGuidByVin(vin);

                // Create the UsedCar
                UsedCarDAO.getInstance().create(usedCarId);
            }

            tradeInLine.setUsedCar(usedCar); // Set the usedCar in the TradeInLine
        } catch (Exception e) {
            //e.printStackTrace(); // - We typically won't find a UsedCar Vin this way... so lets just ignore this message.
        }

        Cache.getInstance().put(tradeInLine.getId(), tradeInLine); // put it in cache... just in case...
        return tradeInLine;
    }//create


    ////////////////////////////////////////////
    ///   READ methods
    /**
     * Reads an existing tradeInLine from the database     */
    public TradeInLine read(String id) throws DataException {
        // Check Cache
        if (Cache.getInstance().containsKey(id)) {
            return (TradeInLine) Cache.getInstance().get(id);
        }

        throw new DataException("TradeInLine object not found in cache, use the Sale Object to access tradeInLines.");
/*
        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try {
            return read(id, conn);
        } catch (Exception e) {
            throw new DataException("An error occurred while reading the TradeInLine object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
*/
    }
    /** External method that allows a tradeInLine to be read by saleId and usedCar id */
    public TradeInLine read(String saleId, String usedCarId) throws DataException{
        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try {
            return read(saleId, usedCarId, conn);
        } catch (Exception e) {
            throw new DataException("An error occurred while reading the TradeInLine object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /**
     * Internal method to read an existing tradeInLine from the database
     * @return tradeInLine
     */
    synchronized TradeInLine read(String saleId, String usedCarId, Connection conn) throws Exception {
        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM trade_in_line where sale_id=? and used_car_id=?");

        try {
            stmt.setString(1, saleId);
            stmt.setString(2, usedCarId);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next()) {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("TradeInLine with saleId '" + saleId + "' and used_car_id '" + usedCarId + "' not found.");
        } finally {
            stmt.close();
        }
    }//read

    /** Internal method to create a tradeInLine object from a record  */
    synchronized TradeInLine readRecord(ResultSet rs, Connection conn) throws Exception {
//        if (Cache.getInstance().containsKey(rs.getString("id"))) {
//            return (TradeInLine) Cache.getInstance().get(rs.getString("id"));
//        }
        // Create new tradeInLine object
        TradeInLine tradeInLine = new TradeInLine(GUID.generate()); // generating a new guid... I don't see how we could find this in the cache

        // TradeInLine is already in DB
        tradeInLine.setObjectAlreadyInDB(true);

        // Put tradeInLine in cache
        Cache.getInstance().put(tradeInLine.getId(), tradeInLine);

        // Set TradeInLine Attributes
        tradeInLine.setSaleId(rs.getString("sale_id"));
        tradeInLine.setUsedCarId(rs.getString("used_car_id"));
        tradeInLine.setTradeInValue(rs.getDouble("trade_in_value"));
        tradeInLine.setMileage(rs.getInt("mileage"));

        // TradeInLine is dirty and needs to be saved to DB
        tradeInLine.setDirty(false);

        return tradeInLine;
    }//readRecord


    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing tradeInLine in the database */
    public void save(TradeInLine tradeInLine) throws DataException {
        // get a connection from the pool, save all of the sales first using the other tradeInLine method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try {
            save(tradeInLine, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e) {
            try {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2) {
                throw new DataException("Could not roll back the tradeInLine database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the tradeInLine object information.", e);
        } finally {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a tradeInLine in the database */
    void save(TradeInLine tradeInLine, Connection conn) throws Exception {
        // put the tradeInLine object to cache
        Cache.getInstance().put(tradeInLine.getId(), tradeInLine);

        // decide whether to update or insert into the database
        if (tradeInLine.isObjectAlreadyInDB() && tradeInLine.isDirty()) {
            update(tradeInLine, conn);
        } else if (tradeInLine.isDirty()) {
            insert(tradeInLine, conn);
        }//if
    }

    /** External method to update or create a tradeInLine in the database without committing connection */
    public void saveNoCommit(TradeInLine tradeInLine, Connection conn) throws Exception {
        // Get Already in Databsae
        Boolean alreadyInDB = tradeInLine.isObjectAlreadyInDB();

        // decide whether to update or insert into the database
        if (alreadyInDB) {
            update(tradeInLine, conn);
        } else {
            insert(tradeInLine, conn);
        }

        // Save No Commit our pretty physical car
        PhysicalCarDAO.getInstance().saveNoCommit(tradeInLine.getUsedCar(), conn);
    }

    /** Saves an existing tradeInLine to the database */
    private void update(TradeInLine tradeInLine, Connection conn) throws Exception {
        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("UPDATE trade_in_line SET trade_in_value=?, mileage=?  WHERE sale_id=? AND used_car_id=?");
        try {
            // Get TradeInLine Attributes and add them to the statement
            stmt.setDouble(1, tradeInLine.getTradeInValue());
            stmt.setInt(2, tradeInLine.getMileage());
            stmt.setString(3, tradeInLine.getSaleId());
            stmt.setString(4, tradeInLine.getUsedCarId());
            stmt.execute();

            // TradeInLine is clean!
            tradeInLine.setDirty(false);
        } finally {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Inserts a new tradeInLine into the database */
    private void insert(TradeInLine tradeInLine, Connection conn) throws Exception {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO trade_in_line (sale_id, used_car_id, trade_in_value, mileage) VALUES (?, ?, ?, ?)");
        try {
            // Set TradeInLine Attributes
            stmt.setString(1, tradeInLine.getSaleId());
            stmt.setString(2, tradeInLine.getUsedCarId());
            stmt.setDouble(3, tradeInLine.getTradeInValue());
            stmt.setInt(4, tradeInLine.getMileage());
            stmt.execute();

            tradeInLine.setObjectAlreadyInDB(true); // now in DB
            tradeInLine.setDirty(false); // object is clean
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            stmt.close(); // close the prepared statement
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(TradeInLine tradeInLine) throws DataException {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }

    ////////////////////////////////////////////////
    ///   SEARCH methods

}
